USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[LapInfo] スクリプト日付: 2024/10/16 10:00:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LapInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LapInfo]
GO

CREATE PROCEDURE [dbo].[LapInfo]
    @TAIKAINO int,
    @KYOGINO int,
    @ROUNDNO int,
    @SUBNO int,
    @KUMINO int
AS
SELECT
    K.TAIKAINO,
    K.KYOGINO,
    K.SYUMOKUNO,
    LL.LAPNO,
    LL.ID,
    LL.LAPMEI,
    LL.LAPKANA,
    RL.KYOGIFLG2
FROM
    TBL_KYOGI K
    INNER JOIN (
        SELECT
            L.SYUMOKUNO,
            L.LAPNO,
            L.ID,
            LM.LAPMEI,
            LM.LAPKANA
        FROM
            TBL_LAP L
            INNER JOIN TBL_LAP_MASTER LM ON L.LAPNO = LM.LAPNO
    ) LL ON K.SYUMOKUNO = LL.SYUMOKUNO
    LEFT JOIN (
        SELECT
            *
        FROM
            TBL_RESULT_LAP
        WHERE
            TAIKAINO = @TAIKAINO
            AND KYOGINO = @KYOGINO
            AND SUBNO = @SUBNO
            AND ROUNDNO = @ROUNDNO
            AND KUMINO = @KUMINO
    ) RL ON K.TAIKAINO = RL.TAIKAINO
    AND K.KYOGINO = RL.KYOGINO
    AND LL.LAPNO = RL.LAPNO
WHERE
    K.TAIKAINO = @TAIKAINO
    AND K.KYOGINO = @KYOGINO
ORDER BY
    LL.ID